package com.sx.dao.daoImpl;

import com.sx.dao.OptionRecordDao;
import com.sx.pojo.OptionRecords;
import com.sx.utils.DatabaseUtil;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import static com.mysql.cj.util.TimeUtil.DATE_FORMATTER;

//操作记录表
public class OptionRecordDaoImpl implements OptionRecordDao {
    private static final String QUERY_MAX_ID = "SELECT id FROM optionrecords ORDER BY id DESC LIMIT 1";
    private static final Logger logger = LogManager.getLogger(OptionRecordDaoImpl.class);
    // 在类级别定义和初始化 DateTimeFormatter
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm");


    @Override
    public String getMaxOperationRecordId() {
        try (
                Connection connection = DatabaseUtil.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(QUERY_MAX_ID);
                ResultSet resultSet = preparedStatement.executeQuery()) {
            if (resultSet.next()) {
                return resultSet.getString("id");
            }
        } catch (SQLException e) {
            // 处理异常，这里简单返回 null，表示当前没有操作记录
            logger.error("数据库操作异常", e);
            //e.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * 向数据库中添加选项记录
     *
     * @param operationRecordId 操作记录ID
     * @param personId          个人ID
     * @param personName        个人姓名
     * @param beOption          操作前的选项
     * @param afOption          操作后的选项
     * @param dateTime          记录的创建时间
     * @param flag              记录标志
     * @param type              记录类型
     *                          <p>
     *                          此方法用于将选项操作记录插入到数据库中它接受操作前后的选项、个人信息、记录ID、创建时间和标志作为参数
     *                          如果dateTime参数为null，则抛出IllegalArgumentException异常，因为创建时间不能为空
     *                          使用try-with-resources语句确保数据库连接和PreparedStatement在使用后能被正确关闭
     *                          如果数据库操作异常，记录错误日志并抛出RuntimeException异常
     */
    @Override
    public void addOptionRecord(String operationRecordId, String personId, String personName, String beOption, String afOption, LocalDateTime dateTime, int flag, int type) {
        // SQL插入语句，用于将数据插入到optionrecords表中
        final String sql = "INSERT INTO optionrecords (id, personId, personName, beOption, afOption, CreateTime, flag, type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        // 检查dateTime参数是否为null，如果为null，则抛出异常
        if (dateTime == null) {
            throw new IllegalArgumentException("dateTime cannot be null");
        }

        // 使用try-with-resources语句获取数据库连接和PreparedStatement，确保资源在使用后能被正确关闭
        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            // 设置PreparedStatement参数
            preparedStatement.setString(1, operationRecordId);
            preparedStatement.setString(2, personId);
            preparedStatement.setString(3, personName);
            preparedStatement.setString(4, beOption);
            preparedStatement.setString(5, afOption);
            preparedStatement.setTimestamp(6, Timestamp.valueOf(dateTime));
            preparedStatement.setInt(7, flag);
            preparedStatement.setInt(8, type);

            // 执行更新操作，将记录插入到数据库中
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            // 记录数据库操作异常日志，并抛出运行时异常
            logger.error("数据库操作异常", e);
            throw new RuntimeException("数据库插入操作失败", e);
        }
    }

    @Override
    public ArrayList<OptionRecords> getAllOptionRecord() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            if (conn != null) {
                String sql = "SELECT * FROM optionrecords";
                stmt = conn.prepareStatement(sql);
                rs = stmt.executeQuery();
                ArrayList<OptionRecords> optionRecords = new ArrayList<>();
                while (rs.next()) {
                    OptionRecords optionRecord = new OptionRecords();
                    optionRecord.setId(rs.getString("id"));
                    optionRecord.setPersonId(rs.getString("personId"));
                    optionRecord.setPersonName(rs.getString("personName"));
                    optionRecord.setBeOption(rs.getString("beOption"));
                    optionRecord.setAfOption(rs.getString("afOption"));
                    optionRecord.setCreateTime(rs.getTimestamp("CreateTime"));
                    optionRecord.setFlag(rs.getInt("flag"));
                    optionRecord.setType(rs.getInt("type"));
                    optionRecords.add(optionRecord);
                }
                logger.info("查询到操作记录数：{}", optionRecords.size());
                return optionRecords;

            } else {
                logger.error("数据库连接失败");
            }
        } catch (SQLException e) {
            logger.error("执行数据库查询操作失败，SQL语句: {}", stmt, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                logger.error("关闭资源异常", e);
            }
        }
        return null;
    }

    @Override
    public boolean deleteOptionRecordById(String id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DatabaseUtil.getConnection();
            if (conn != null) {
                String sql = "DELETE FROM optionrecords WHERE id = ?";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, id);
                int rowsAffected = stmt.executeUpdate();
                logger.info("删除操作记录，受影响行数：{}", rowsAffected);
                return rowsAffected > 0;
            } else {
                logger.error("数据库连接失败");
            }
        } catch (SQLException e) {
            logger.error("执行数据库删除操作失败，SQL语句: {}", stmt, e);
        } finally {

            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                logger.error("关闭资源异常", e);
            }
        }
        return false;
    }

    public List<OptionRecords> getFilteredOptionRecords(String startDate, String endDate, String operatorId, String operationType, String tableType) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM optionrecords WHERE 1 = 1");
        ArrayList<Object> params = new ArrayList<>();

        if (startDate != null && !startDate.isEmpty()) {
            sqlBuilder.append(" AND createTime >= ?");
            // 更改格式
            try {
                // 将日期字符串解析为 LocalDateTime 对象，并设置时间为当天的开始时间
                LocalDateTime parsedStartDate = LocalDateTime.parse(startDate, DATE_TIME_FORMATTER);
                params.add(Timestamp.valueOf(parsedStartDate));
            } catch (DateTimeParseException e) {
                logger.error("解析 startDate 失败", e);
                return Collections.emptyList(); // 返回空列表或抛出异常，取决于你的需求
            }
        }

        if (endDate != null && !endDate.isEmpty()) {
            sqlBuilder.append(" AND createTime <= ?");
            // 更改格式
            try {
                // 将日期字符串解析为 LocalDateTime 对象，并设置时间为当天的结束时间
                LocalDateTime parsedEndDate = LocalDateTime.parse(endDate, DATE_TIME_FORMATTER);
                params.add(Timestamp.valueOf(parsedEndDate));
            } catch (DateTimeParseException e) {
                logger.error("解析 endDate 失败", e);
                return Collections.emptyList(); // 返回空列表或抛出异常，取决于你的需求
            }
        }
        if (operatorId != null && !operatorId.isEmpty()) {
            sqlBuilder.append(" AND personId =?");
            params.add(operatorId);
        }
        if (operationType != null && !operationType.isEmpty()) {
            sqlBuilder.append(" AND flag =?");
            params.add(Integer.parseInt(operationType));
        }
        if (tableType != null && !tableType.isEmpty()) {
            sqlBuilder.append(" AND type =?");
            params.add(Integer.parseInt(tableType));
        }

        String sql = sqlBuilder.toString();
        List<OptionRecords> records = new ArrayList<>();
        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    OptionRecords record = new OptionRecords(
                            resultSet.getString("id"),
                            resultSet.getString("beOption"),
                            resultSet.getString("afOption"),
                            resultSet.getString("personId"),
                            resultSet.getString("personName"),
                            resultSet.getTimestamp("createTime"),
                            resultSet.getInt("flag"),
                            resultSet.getInt("type")
                    );
                    records.add(record);
                }
            }
        } catch (SQLException e) {
            logger.error("Error occurred while fetching filtered option records", e);
        }
        return records;
    }


}

